package com.surprise.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import lombok.AllArgsConstructor;

import org.apache.commons.collections.CollectionUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.surprise.base.util.SQLUtils;
import com.surprise.db.entity.CUser;
import com.surprise.db.po.User;

/**
 * UserDAO.java
 *
 * @author nick
 * @since 2015年5月24日
 */
@Repository
public class UserDAO extends SimpleDAO<User> {
    public final String columns = "(username,password,portrait,phone,role,type,addtime)";
    public final String insertStatement = "(?,?,?,?,?,?,now())";
    public final String DUPLICATE = " ON DUPLICATE KEY update password=values(password)";

    @AllArgsConstructor
    public static class UserSetter implements BatchPreparedStatementSetter {
        private List<User> items;

        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            User item = items.get(i);
            int index = 1;
            ps.setString(index++, item.getUsername());
            ps.setString(index++, item.getPassword());
            ps.setString(index++, item.getPortrait());
            ps.setString(index++, item.getPhone());
            ps.setInt(index++, item.getRole());
            ps.setInt(index++, item.getType());
        }

        @Override
        public int getBatchSize() {
            return items.size();
        }
    }

    public Long addWithId(final User item) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        StringBuilder sb = new StringBuilder();
        sb.append("insert into ").append(getTableName()).append(columns).append(" values ")
                .append(insertStatement);
        String sql = sb.toString();
        jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                int index = 1;
                ps.setString(index++, item.getUsername());
                ps.setString(index++, item.getPassword());
                ps.setString(index++, item.getPortrait());
                ps.setString(index++, item.getPhone());
                ps.setInt(index++, item.getRole());
            }
        }, keyHolder);
        return keyHolder.getKey().longValue();
    }

    public int add(final User item) {
        StringBuilder sb = new StringBuilder();
        sb.append("insert into ").append(getTableName()).append(columns).append(" values ")
                .append(insertStatement).append(DUPLICATE);
        String sql = sb.toString();
        return jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                int index = 1;
                ps.setString(index++, item.getUsername());
                ps.setString(index++, item.getPassword());
                ps.setString(index++, item.getPortrait());
                ps.setString(index++, item.getPhone());
                ps.setInt(index++, item.getRole());
                ps.setInt(index++, item.getType());
            }
        });
    }

    public void add(List<User> items) {
        StringBuilder sb = new StringBuilder();
        sb.append("insert into ").append(getTableName()).append(columns).append(" values ")
                .append(insertStatement);
        String sql = sb.toString();
        jdbcTemplate.batchUpdate(sql, new UserSetter(items));
    }

    public void update(User user) {
        StringBuilder sb = new StringBuilder();
        sb.append("update ").append(getTableName()).append(
                " set username = ?, password = ?, portrait = ?, role = ?, type = ? where id = ? ");
        String sql = sb.toString();
        jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getPortrait(),
                user.getRole(), user.getType(), user.getId());
    }

    public User findByPhone(String phone, int role) {
    	String sql = "";
    	List<User> ret = null;
    	if (role == 0) {
    		sql = "select * from #table# where phone = ? ";
    		ret = findBySql(sql, phone);
    	} else if (role != 5 && role != 99) {
    		sql = "select * from #table# where phone = ? and role = ? ";
    		ret = findBySql(sql, phone, role);
    	} else {
    		sql = "select * from #table# where phone = ? and role in (5, 99)";
    		ret = findBySql(sql, phone);
    	}
        if (CollectionUtils.isEmpty(ret)) {
            return null;
        }
        return ret.get(0);
    }

    public User login(String phone, String password) {
        String sql = "select * from #table# where phone = ? and password = ?";
        List<User> ret = findBySql(sql, phone, password);
        if (CollectionUtils.isEmpty(ret)) {
            return null;
        }
        return ret.get(0);
    }

    public User queryByid(String userid) {
        String sql = "select * from #table# where id = ?";
        List<User> ret = findBySql(sql,userid);
        if (CollectionUtils.isEmpty(ret)) {
            return null;
        }
        return ret.get(0);
    }
    public int edit(long id, String username, String password, String portrait, String email, String address, int gender,int age,String hobby,String malls) {
        StringBuilder sb = new StringBuilder();

        sb.append("update ").append(getTableName()).append(" set ");

        if (!StringUtils.isEmpty(username)) {
            sb.append(" username = '").append(SQLUtils.escapeSql(username)).append("',");
        }
        if (!StringUtils.isEmpty(password)) {
            sb.append(" password = '").append(SQLUtils.escapeSql(password)).append("',");
        }
        if (!StringUtils.isEmpty(portrait)) {
            sb.append(" portrait = '").append(SQLUtils.escapeSql(portrait)).append("',");
        }
        if (!StringUtils.isEmpty(email)) {
            sb.append(" email = '").append(SQLUtils.escapeSql(email)).append("',");
        }
        if (!StringUtils.isEmpty(address)) {
            sb.append(" address = '").append(SQLUtils.escapeSql(address)).append("',");
        }
        sb.append(" gender = ").append(gender).append(",");
        sb.append(" age = ").append(age).append(",");
        if (!StringUtils.isEmpty(hobby)) {
            sb.append(" hobby = '").append(SQLUtils.escapeSql(hobby)).append("',");
        }
        if (!StringUtils.isEmpty(malls)) {
            sb.append(" malls = '").append(SQLUtils.escapeSql(malls)).append("',");
        }
        sb.deleteCharAt(sb.lastIndexOf(","));
        sb.append(" where id = ").append(id);
        String sql = sb.toString();
        return jdbcTemplate.update(sql);
    }

    public int edit(long id, String username, String portrait, String email, String address, int gender,int age,String hobby,String malls,String floor,String city) {
        StringBuilder sb = new StringBuilder();

        sb.append("update ").append(getTableName()).append(" set ");

        if (!StringUtils.isEmpty(username)) {
            sb.append(" username = '").append(SQLUtils.escapeSql(username)).append("',");
        }
//        if (!StringUtils.isEmpty(portrait)) {
//            sb.append(" portrait = '").append(SQLUtils.escapeSql(portrait)).append("',");
//        }
        if (!StringUtils.isEmpty(email)) {
            sb.append(" email = '").append(SQLUtils.escapeSql(email)).append("',");
        }
        if (!StringUtils.isEmpty(address)) {
            sb.append(" address = '").append(SQLUtils.escapeSql(address)).append("',");
        }
        sb.append(" gender = ").append(gender).append(",");
        sb.append(" age = ").append(age).append(",");
        if (!StringUtils.isEmpty(hobby)) {
            sb.append(" hobby = '").append(SQLUtils.escapeSql(hobby)).append("',");
        }
        if (!StringUtils.isEmpty(malls)) {
            sb.append(" malls = '").append(SQLUtils.escapeSql(malls)).append("',");
        }
        if (!StringUtils.isEmpty(floor)) {
            sb.append(" floor = '").append(SQLUtils.escapeSql(floor)).append("',");
        }
        if (!StringUtils.isEmpty(city)) {
            sb.append(" city = '").append(SQLUtils.escapeSql(city)).append("',");
        }
        sb.deleteCharAt(sb.lastIndexOf(","));
        sb.append(" where id = ").append(id);
        String sql = sb.toString();
        return jdbcTemplate.update(sql);
    }

    public int edit_mallid_brandid(long id,String mallid,String brandid, String name,String dptype,String dpid) {
        StringBuilder sb = new StringBuilder();

        sb.append("update ").append(getTableName()).append(" set ");
        sb.append(" role = '").append("99").append("',");
        if (!StringUtils.isEmpty(mallid)) {
            sb.append(" mallid = '").append(SQLUtils.escapeSql(mallid)).append("',");
        }
        if (!StringUtils.isEmpty(brandid)) {
            sb.append(" brandid = '").append(SQLUtils.escapeSql(brandid)).append("',");
        }
        if (!StringUtils.isEmpty(name)) {
            sb.append(" name = '").append(SQLUtils.escapeSql(name)).append("',");
        }
        if (!StringUtils.isEmpty(dptype)) {
            sb.append(" dptype = '").append(SQLUtils.escapeSql(dptype)).append("',");
        }
        if (!StringUtils.isEmpty(dpid)) {
            sb.append(" dpid = '").append(SQLUtils.escapeSql(dpid)).append("',");
        }
        sb.deleteCharAt(sb.lastIndexOf(","));
        sb.append(" where id = ").append(id);
        String sql = sb.toString();
        return jdbcTemplate.update(sql);
    }

    public int edit_portrait_name(long id,String portrait,String name) {
        StringBuilder sb = new StringBuilder();

        sb.append("update ").append(getTableName()).append(" set ");

        if (!StringUtils.isEmpty(portrait)) {
            sb.append(" portrait = '").append(SQLUtils.escapeSql(portrait)).append("',");
        }
        if (!StringUtils.isEmpty(name)) {
            sb.append(" username = '").append(SQLUtils.escapeSql(name)).append("',");
        }
        sb.deleteCharAt(sb.lastIndexOf(","));
        sb.append(" where id = ").append(id);
        String sql = sb.toString();
        return jdbcTemplate.update(sql);
    }

    public int edit_token(long id,String token) {
        StringBuilder sb = new StringBuilder();

        sb.append("update ").append(getTableName()).append(" set ");

        if (!StringUtils.isEmpty(token)) {
            sb.append(" token = '").append(SQLUtils.escapeSql(token)).append("',");
        }
        sb.deleteCharAt(sb.lastIndexOf(","));
        sb.append(" where id = ").append(id);
        String sql = sb.toString();
        return jdbcTemplate.update(sql);
    }



    public int edit(String phone, String password) {
        StringBuilder sb = new StringBuilder();
        sb.append("update ").append(getTableName()).append(" set password = ? where phone = ?");
        String sql = sb.toString();
        return jdbcTemplate.update(sql, password, phone);
    }

    public List<User> getUserByRole(int role) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from ").append(getTableName()).append(" where role = ? ");
        String sql = sb.toString();
        return jdbcTemplate.query(sql, new Object[] {role},
                new BeanPropertyRowMapper<User>(User.class));
    }

    public int updatePrizeForPhone(String phone, long userid) {
    	StringBuilder sb = new StringBuilder();
    	sb.append("update prize set userid = ? where phone = ? and userid = 0");
    	String sql = sb.toString();
    	return jdbcTemplate.update(sql, userid, phone);
    }

    public List<CUser> findByMallidForEmchat(String mallid) {
    	StringBuilder sb = new StringBuilder();
        sb.append("select * from ").append(getTableName()).append(" where mallid = ? ");
        String sql = sb.toString();
        return jdbcTemplate.query(sql, new Object[] {mallid},
                new BeanPropertyRowMapper<CUser>(CUser.class));
    }

    public CUser findCUserForPhone(String phone) {
    	StringBuilder sb = new StringBuilder();
        sb.append("select * from user where phone = ? ");
        String sql = sb.toString();
        List<CUser> list = jdbcTemplate.query(sql, new Object[] {phone},
                new BeanPropertyRowMapper<CUser>(CUser.class));
        if (CollectionUtils.isEmpty(list)) {
            return null;
        }
        return list.get(0);
    }
    
    public List<CUser> findCUserForWhereFree(String whereFree) {
    	StringBuilder sb = new StringBuilder();
        sb.append("select * from user ? ");
        String sql = sb.toString();
        List<CUser> list = jdbcTemplate.query(sql, new Object[] {whereFree},
                new BeanPropertyRowMapper<CUser>(CUser.class));
        return list;
    }

    public List<CUser> findALLForEmchat() {
    	StringBuilder sb = new StringBuilder();
        sb.append("select * from ").append(getTableName());
        String sql = sb.toString();
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<CUser>(CUser.class));
    }
}
